﻿using System;
using System.Collections.Generic;
using LantinxShare.LBS;
using LantixLBSServer.Web.DbAccess.Helper;
using System.Data;

namespace LantixLBSServer.Web.DbAccess
{
    public class UserManager : IManager
    {
        /// <summary>
        /// Get All users
        /// </summary>
        /// <returns></returns>
        public List<GUser> GetUsers()
        {
            List<GUser> list = new List<GUser>();
            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                IDataReader reader = db.ExecuteDataReader(@"select t1.id,t1.username,t1.password,t1.useralias,t1.phone,
                        t2.id,t2.roleid,t2.rolename from t_user t1,t_role t2 where t1.roleid= t2.roleid", false);
                while (reader.Read())
                {
                    GUser user = new GUser()
                    {

                        Id = reader.GetInt32(0),
                        UserName = reader.GetString(1),
                        Password = reader.GetString(2),
                        UserAlias = reader.GetString(3),
                        Phone = reader.GetString(4),
                        Role = new GRole()
                        {
                            Id = reader.GetInt32(5),
                            RoleId = reader.GetInt32(6),
                            RoleName = reader.GetString(7)
                        },
                    };
                    list.Add(user);
                }
            }, () => { db.CloseConnection(); });


            return list;
        }

        public GUser GetUser(String useralias, String password)
        {
            SqliteDB db = this.GetDB();
            GUser user = null;
            Execute(() =>
            {
                db.AddParameter("@useralias");
                db.AddParameter("@password");
                db.SetParameterValue("@useralias", useralias);
                db.SetParameterValue("@password", password);
                IDataReader reader = db.ExecuteDataReader(@"select t1.id,t1.username,t1.password,t1.useralias,t1.phone,
                        t2.id,t2.roleid,t2.rolename from t_user t1,t_role t2 where t1.roleid= t2.roleid and t1.useralias=@useralias and t1.password=@password ", false);

                while (reader.Read())
                {
                    user = new GUser()
                    {

                        Id = reader.GetInt32(0),
                        UserName = reader.GetString(1),
                        Password = reader.GetString(2),
                        UserAlias = reader.GetString(3),
                        Phone = reader.GetString(4),
                        Role = new GRole()
                        {
                            Id = reader.GetInt32(5),
                            RoleId = reader.GetInt32(6),
                            RoleName = reader.GetString(7)
                        },
                    };
                    break;
                }
            }, () => { db.CloseConnection(); });

            return user;
        }

        public bool UpdateUser(GUser user)
        {
            bool result = false;
            SqliteDB db = this.GetDB();

            Execute(() =>
            {
                db.AddParameter("@username");
                db.AddParameter("@password");
                db.AddParameter("@userAlias");
                db.AddParameter("@phone");
                db.AddParameter("@roleid");
                db.AddParameter("@id");
                db.SetParameterValue("@username", user.UserName);
                db.SetParameterValue("@password", user.Password);
                db.SetParameterValue("@userAlias", user.UserAlias);
                db.SetParameterValue("@phone", user.Phone);
                db.SetParameterValue("@roleid", user.Role.RoleId);
                db.SetParameterValue("@id", user.Id);

                if (db.ExecuteNonQuery(@"update t_user set username=@username,password=@password,userAlias=@userAlias,phone=@phone,roleid=@roleid  where id=@id ", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }

        public bool InsertUser(String userName, String userAlias, String password, String phone, int roleid)
        {
            bool result = false;
            SqliteDB db = this.GetDB();

            Execute(() =>
            {
                db.AddParameter("@username");
                db.AddParameter("@password");
                db.AddParameter("@userAlias");
                db.AddParameter("@phone");
                db.AddParameter("@roleid");
                db.SetParameterValue("@username", userName);
                db.SetParameterValue("@userAlias", userAlias);
                db.SetParameterValue("@password", password);
                db.SetParameterValue("@phone", phone);
                db.SetParameterValue("@roleid", roleid);

                if (db.ExecuteNonQuery(@"insert into t_user(username,password,userAlias,phone,roleid) values( @username,@password,@userAlias,@phone,@roleid )", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }

        public bool DeleteUserByAlias(string userAlias)
        {
            bool result = false;

            if (string.IsNullOrEmpty(userAlias))
                return result;

            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@useralias");
                db.SetParameterValue("@useralias", userAlias);

                if (db.ExecuteNonQuery(@"delete from t_user where useralias=@useralias ", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }

        public bool DeleteUserById(String userId)
        {
            bool result = false;

            if (string.IsNullOrEmpty(userId))
                return result;

            SqliteDB db = this.GetDB();
            Execute(() =>
            {
                db.AddParameter("@id");
                db.SetParameterValue("@id", userId);

                if (db.ExecuteNonQuery(@"delete from t_user where id=@id ", false, true) > 0)
                {
                    result = true;
                }
            });
            return result;
        }


    }// end for class
}